package com.blockchain.console.service.financial.achieve;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.blockchain.common.enums.PlatformFundType;
import com.blockchain.common.enums.TradingType;
import com.blockchain.common.enums.WithdrawStatus;
import com.blockchain.common.enums.XlbType;
import com.blockchain.console.config.ConsoleConst;
import com.blockchain.console.service.financial.TxglManage;
import com.blockchain.console.service.financial.entity.BankCard;
import com.blockchain.console.service.financial.entity.TxglRecord;
import com.blockchain.console.service.financial.query.TxglRecordQuery;
import com.blockchain.console.service.system.entity.Bank;
import com.blockchain.framework.config.ConfigureProvider;
import com.blockchain.framework.data.sql.SQLConnectionProvider;
import com.blockchain.framework.resource.ResourceNotFoundException;
import com.blockchain.framework.service.ServiceFactory;
import com.blockchain.framework.service.ServiceResource;
import com.blockchain.framework.service.exception.ParameterException;
import com.blockchain.framework.service.query.ArrayParser;
import com.blockchain.framework.service.query.ItemParser;
import com.blockchain.framework.service.query.Paging;
import com.blockchain.framework.service.query.PagingResult;
import com.blockchain.util.EmailUtil;
import com.blockchain.util.StringHelper;
import com.blockchain.util.parser.DateParser;
import com.blockchain.util.parser.EnumParser;
import com.blockchain.util.parser.IntegerParser;
import com.blockchain.variables.P2PConst;
import com.blockchain.variables.defines.SystemVariable;

public class TxglManageImpl extends AbstractFinancialService implements
		TxglManage {

	public static class ExtractionManageFactory implements
			ServiceFactory<TxglManage> {

		@Override
		public TxglManage newInstance(ServiceResource serviceResource) {
			return new TxglManageImpl(serviceResource);
		}
	}


	public TxglManageImpl(ServiceResource serviceResource) {
		super(serviceResource);

	}


	@Override
	public PagingResult<TxglRecord> search(TxglRecordQuery query,
			Paging paging) throws Throwable {
		StringBuilder sql = new StringBuilder("SELECT T1.F01, T2.F02, T3.F02 name, T5.F02 AS BANK,")
		.append("T4.F04 szd, T4.F05 khzh, T4.F06 AS F15, T1.F04 AS F16, T1.F06, T1.F08, T1.F12, T1.F10,")
		.append("T1.F07 AS STATUS,T2.F01 AS USERID,T1.F03 AS CREATETIME,T1.F11,T1.F09,T4.F09 AS cs,T4.F11 as phoneNumber,T1.F14 as rate,T1.F15 as cny ")
		.append(" FROM T6034 T1");
		sql.append(" LEFT JOIN T6010 T2 ON T1.F02 = T2.F01");
		sql.append(" LEFT JOIN T6011 T3 ON T1.F02 = T3.F01");
		sql.append(" LEFT JOIN T6024 T4 ON T1.F05 = T4.F01");
		sql.append(" LEFT JOIN T6052 T5 ON T4.F03=T5.F01 WHERE 1=1");
		ArrayList<Object> parameters = new ArrayList<>();
		if (query != null) {
			SQLConnectionProvider sqlConnectionProvider = getSQLConnectionProvider();
			int lsj = query.getLsh();
			String yhm = query.getYhm();
			String xm = query.getXm();
			String yhkh = query.getYhkh();
			WithdrawStatus status = query.getStatus();
			if (lsj > 0) {
				sql.append(" AND T1.F01 = ?");
				parameters.add(lsj);
			}
			if (!StringHelper.isEmpty(yhm)) {
				sql.append(" AND  T2.F02 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(yhm));
			}
			if (!StringHelper.isEmpty(xm)) {
				sql.append(" AND  T3.F02 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(xm));
			}
			if (!StringHelper.isEmpty(yhkh)) {
				sql.append(" AND T4.F06 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(yhkh));
			}
			int bankId = query.getBankId();
			if (bankId > 0) {
				sql.append(" AND T4.F03 = ?");
				parameters.add(bankId);
			}
			Timestamp datetime = query.getStartExtractionTime();
			if (datetime != null) {
				if (status == WithdrawStatus.WSH) {
					sql.append(" AND DATE(T1.F03)>=?");
				} else if (status == WithdrawStatus.SHTG
						|| status == WithdrawStatus.SHSB) {
					sql.append(" AND DATE(T1.F08) >= ?");
				} else if (status == WithdrawStatus.TXCG
						|| status == WithdrawStatus.TXSB) {
					sql.append(" AND DATE(T1.F12) >= ?");
				}
				parameters.add(datetime);
			}
			datetime = query.getEndExtractionTime();
			if (datetime != null) {
				if (status == WithdrawStatus.WSH) {
					sql.append(" AND DATE(T1.F03)<=?");
				} else if (status == WithdrawStatus.SHTG
						|| status == WithdrawStatus.SHSB) {
					sql.append(" AND DATE(T1.F08) <= ?");
				} else if (status == WithdrawStatus.TXCG
						|| status == WithdrawStatus.TXSB) {
					sql.append(" AND DATE(T1.F12) <= ?");
				}
				parameters.add(datetime);
			}
			if (status != null) {
				sql.append(" AND T1.F07=?");
				parameters.add(status);
			}
			if (status == WithdrawStatus.WSH) {
				sql.append(" ORDER BY T1.F03 DESC");
			} else if (status == WithdrawStatus.SHTG
					|| status == WithdrawStatus.SHSB) {
				sql.append(" ORDER BY T1.F08 DESC");
			} else if (status == WithdrawStatus.TXCG) {
				sql.append(" ORDER BY T1.F12 DESC");
			}
			
		}
		return selectPaging(getConnection(ConsoleConst.DB_USER),
				new ArrayParser<TxglRecord>() {
					ArrayList<TxglRecord> list = new ArrayList<TxglRecord>();

					public TxglRecord[] parse(ResultSet rst)
							throws SQLException {
						while (rst.next()) {
							TxglRecord user = new TxglRecord();
							user.id = rst.getInt(1);
							user.loginName = rst.getString(2);
							user.userName = rst.getString(3);
							user.extractionBank = rst.getString(4);
							user.location = rst.getString(5);
							user.subbranch = rst.getString(6);
							user.bankId = rst.getString(7);
							user.extractionAmount = rst.getBigDecimal(8);
							user.charge = rst.getBigDecimal(9);
							user.checkDateTime = rst.getTimestamp(10);
							user.applyDateTime = rst.getTimestamp(11);
							user.approver = getName(rst.getInt(12));
							user.status = EnumParser.parse(
									WithdrawStatus.class, rst.getString(13));
							user.userId = rst.getInt(14);
							user.createTime = rst.getTimestamp(15);
							user.loan = getName(rst.getInt(16));
							user.remark = rst.getString(17);
							user.cs=rst.getString(18);
							user.phoneNumber=rst.getString(19);
							user.rate=rst.getBigDecimal(20);
							user.cny=rst.getBigDecimal(21);
							list.add(user);
						}
						return list.toArray(new TxglRecord[list.size()]);
					}
				}, paging, sql.toString(), parameters);

	}
	
	/**
	 * 查询审核人
	 * 
	 * @param userId
	 * @return
	 */
	private String getName(int id) throws SQLException {
		try (PreparedStatement pstmt = getConnection().prepareStatement(
				"SELECT F02 FROM T7011 WHERE F01=?")) {
			pstmt.setInt(1, id);
			try (ResultSet resultSet = pstmt.executeQuery()) {
				if (resultSet.next()) {
					return resultSet.getString(1);
				}
			}
		}
		return "";
	}

	@Override
	public TxglRecord get(int id) throws Throwable {
		if (id <= 0) {
			throw new ParameterException("指定的记录不存在.");
		}
		StringBuilder sql = new StringBuilder(
				"SELECT T1.F01 AS F01, T2.F02 AS F02, T3.F02 name, T5.F02 AS txyh,")
		.append("T4.F04 AS F05, T4.F05 AS F06, T4.F06 AS F07, T1.F04 AS F08, T1.F06 AS F09,")
		.append("T1.F08 AS F10, T1.F12 AS F11, T1.F10 AS F12, T1.F07 AS F13,T2.F01 AS F14,")
		.append("T1.F03 AS F15,T1.F11 AS F16,T1.F09 AS F12,T1.F14 AS rate,T1.F15 as cny ")
		.append(" FROM T6034 T1");
		sql.append(" INNER JOIN T6010 T2 ON T1.F02 = T2.F01");
		sql.append(" INNER JOIN T6011 T3 ON T1.F02 = T3.F01");
		sql.append(" LEFT JOIN T6024 T4 ON T1.F05 = T4.F01");
		sql.append(" LEFT JOIN T6052 T5 ON T4.F03=T5.F01");
		sql.append(" WHERE T1.F01=?");
		return select(getConnection(ConsoleConst.DB_USER),
				new ItemParser<TxglRecord>() {
					public TxglRecord parse(ResultSet rst) throws SQLException {
						TxglRecord user = new TxglRecord();
						if (rst.next()) {
							user.id = rst.getInt(1);
							user.loginName = rst.getString(2);
							user.userName = rst.getString(3);
							user.extractionBank = rst.getString(4);
							user.location = rst.getString(5);
							user.subbranch = rst.getString(6);
							user.bankId = rst.getString(7);
							user.extractionAmount = rst.getBigDecimal(8);
							user.charge = rst.getBigDecimal(9);
							user.checkDateTime = rst.getTimestamp(10);
							user.applyDateTime = rst.getTimestamp(11);
							user.approver = getName(rst.getInt(12));
							user.status = EnumParser.parse(
									WithdrawStatus.class, rst.getString(13));
							user.userId = rst.getInt(14);
							user.createTime = rst.getTimestamp(15);
							user.loan = getName(rst.getInt(16));
							user.remark = rst.getString(17);
							user.rate = rst.getBigDecimal(18);
							user.cny = rst.getBigDecimal(19);
						}
						return user;
					}
				}, sql.toString(), id);
	}

	/**
	 * 更新对象记录
	 * 
	 * @param id
	 * @param status
	 * @param check_reason
	 * @throws Throwable
	 */
	private void update(int id, WithdrawStatus status, String check_reason)
			throws Throwable {
		TxglRecord txglRecord = get(id);
		switch (status) {
		case SHTG:
			break;
		case SHSB:
			update2(check_reason, txglRecord);
			break;
		case TXCG:
			update3(id, check_reason, txglRecord);
			break;
		case TXSB:
			update4(check_reason, txglRecord);
			break;
		default:
			return;
		}
	}

	/**
	 * 审核失败
	 * 
	 * @param check_reason
	 * @param txglRecord
	 * @throws SQLException
	 * @throws ResourceNotFoundException
	 */
	private void update2(String check_reason, TxglRecord txglRecord)
			throws Throwable {
		if (txglRecord == null) {
			throw new ParameterException("提现记录不存在");
		}
		BigDecimal lock = txglRecord.extractionAmount.add(txglRecord.charge);
		String select = "SELECT F03 FROM T6023 WHERE F01=? FOR UPDATE";
		BigDecimal djAmount = selectBigDecimal(
				getConnection(ConsoleConst.DB_USER), select, txglRecord.userId);
		if (djAmount.compareTo(lock) < 0) {
			lock = djAmount;
		}
		String update = "UPDATE T6023 SET F03=F03-?,F04=F04+? WHERE F01=?";
		execute(getConnection(ConsoleConst.DB_USER), update, lock, lock,
				txglRecord.userId);
		//发送短信
		smssb(txglRecord.userId,txglRecord.extractionAmount.add(txglRecord.charge));
	}

	/**
	 * 提现成功
	 * 
	 * @param id
	 * @param check_reason
	 * @param txglRecord
	 */
	private void update3(int id, String check_reason, TxglRecord txglRecord)
			throws Throwable {
		if (txglRecord == null) {
			throw new ParameterException("提现记录不存在");
		}
		// 查询用户资金总额
		String select = "SELECT F02 FROM T6023 WHERE F01=? FOR UPDATE";
		String selectT6023 = "SELECT F03 FROM T6023 WHERE F01=?";
		String selectT7013 = "SELECT F01 FROM T7013 FOR UPDATE";
		BigDecimal total = selectBigDecimal(ConsoleConst.DB_USER, select,
				txglRecord.userId);
		BigDecimal djAmount = selectBigDecimal(ConsoleConst.DB_USER,
				selectT6023, txglRecord.userId);
		// 查询平台资金余额
				BigDecimal sysBalance = selectBigDecimal(ConsoleConst.DB_CONSOLE,
						selectT7013);
		
		// 锁定金额
		BigDecimal lock = txglRecord.extractionAmount.add(txglRecord.charge);
		Timestamp now = new Timestamp(System.currentTimeMillis());
		if (djAmount.compareTo(lock) < 0) {
			lock = djAmount;
		}
		// 提现成功更新用户账户总表
		execute(getConnection(ConsoleConst.DB_USER),
				"UPDATE T6023 SET F02=F02-?,F03=F03-? WHERE F01=?", lock, lock,
				txglRecord.userId);
		// 添加用户资金交易记录
		String t6032 = "INSERT INTO T6032 SET F02=?,F03=?,F04=?,F05=?,F06=?,F07=?,F08=?,F09=?";
		execute(getConnection(ConsoleConst.DB_USER), t6032, txglRecord.userId,
				TradingType.CGTX, now, 0, txglRecord.extractionAmount,
				total.subtract(txglRecord.extractionAmount), id,
				TradingType.CGTX.getName());
		execute(getConnection(ConsoleConst.DB_USER), t6032, txglRecord.userId,
					TradingType.TXSXF, now, 0, txglRecord.charge,
					total.subtract(lock), id, TradingType.TXSXF.getName());
		
		// 更新平台资金账户
		String updateT7013 = "UPDATE T7013 SET F01=F01+?,F02=F02+? ";
		execute(getConnection(), updateT7013, txglRecord.charge,
				txglRecord.charge);
		// 添加平台资金交易记录
		String t7014 = "INSERT INTO t7014 SET F02=?,F03=?,F04=?,F05=?,F06=?,F07=?,F08=?,F09=?";
		execute(getConnection(ConsoleConst.DB_CONSOLE), t7014,PlatformFundType.TXSXF,now,
				txglRecord.charge, 0, sysBalance.add(txglRecord.charge),id,"提现审核通过扣除手续费",txglRecord.userId);
		//发送短信
		smscg(txglRecord.userId,txglRecord.extractionAmount.add(txglRecord.charge));
	}

	/**
	 * 发送提现成功短信 2017年8月1日13:47:20
	 * 
	 * @throws IOException
	 */
	private void smscg(int userid, BigDecimal je) throws SQLException, IOException {
		if (userid > 0) {
			ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
			String iphone = selectString(P2PConst.DB_USER, "SELECT F02 FROM T6010 WHERE F01=?", userid);// 发送短信
			String english = "Dear "+EmailUtil.NIKENAME+" user, congratulations on your success debited SMS_TX_JE . please check your balance. <br><br>";
			String s = english + configureProvider.getProperty(SystemVariable.SMS_TX_JE);
			String content = s.replaceAll("SMS_TX_JE", je.toString());
			EmailUtil.sendHtmlMail(iphone,EmailUtil.NIKENAME, content+"<br><br><br><br>", "系统错误", true);
		}
	}

	/**
	 * 发送提现失败短信 2017年8月3日09:50:27
	 * 
	 * @throws IOException
	 */
	private void smssb(int userid, BigDecimal je) throws SQLException, IOException {
		if (userid > 0) {
			ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
			String iphone = selectString(P2PConst.DB_USER, "SELECT F02 FROM T6010 WHERE F01=?", userid);// 发送短信
			String english = "Dear "+EmailUtil.NIKENAME+" user, your application for SMS_TXSB has not been approved. Please contact customer service for specific reasons. Thank you for your application. <br><br>";
			String s = english + configureProvider.getProperty(SystemVariable.SMS_TXSB);
			String content = s.replaceAll("SMS_TXSB", je.toString());
			EmailUtil.sendHtmlMail(iphone,EmailUtil.NIKENAME, content+"<br><br><br><br>", "系统错误", true);
		}
	}
	/**
	 * 提现失败
	 * 
	 * @param check_reason
	 * @param txglRecord
	 * @throws Throwable 
	 */
	private void update4(String check_reason, TxglRecord txglRecord)
			throws Throwable {
		if (txglRecord == null) {
			throw new ParameterException("提现记录不存在");
		}
		String select = "SELECT F03 FROM T6023 WHERE F01=? FOR UPDATE";
		BigDecimal djAmount = selectBigDecimal(ConsoleConst.DB_USER, select,
				txglRecord.userId);
		// 锁定金额
		BigDecimal lock = txglRecord.extractionAmount.add(txglRecord.charge);
		if (djAmount.compareTo(lock) < 0) {
			lock = djAmount;
		}
		String t6023 = "UPDATE T6023 SET F03=F03-?,F04=F04+? WHERE F01=?";
		execute(getConnection(ConsoleConst.DB_USER), t6023, lock, lock,
				txglRecord.userId);
		//发送短信
		smssb(txglRecord.userId,txglRecord.extractionAmount.add(txglRecord.charge));
	}

	@Override
	public void check(WithdrawStatus status, String check_reason, int id)
			throws Throwable {
		serviceResource.openTransactions();
		if (id <= 0) {
			throw new ParameterException("指定的记录不存在.");
		}
		if (checkStatus(status, id)) {
			return;
		}
		String update1 = "UPDATE T6034 SET F07=?,F08=?,F09=?,F10=? WHERE F01=?";
		String update2 = "UPDATE T6034 SET F07=?,F09=?,F11=?,F12=? WHERE F01=?";
		if (status == WithdrawStatus.SHSB || status == WithdrawStatus.SHTG) {
			execute(getConnection(ConsoleConst.DB_USER), update1, status,
					new Timestamp(System.currentTimeMillis()), check_reason,
					serviceResource.getSession().getAccountId(), id);
		} else if (status == WithdrawStatus.TXCG
				|| status == WithdrawStatus.TXSB) {
			execute(getConnection(ConsoleConst.DB_USER), update2, status,
					check_reason, serviceResource.getSession().getAccountId(),
					new Timestamp(System.currentTimeMillis()), id);
		}
		if (status == WithdrawStatus.TXCG) {
			//推荐人赠送
			int userId = selectInt(P2PConst.DB_USER, "SELECT F02 FROM T6034 WHERE F01=?", id);
			BigDecimal txje = selectBigDecimal(P2PConst.DB_USER, "SELECT F04 FROM T6034 WHERE F01=?", id);
			String yqrm = selectString(P2PConst.DB_USER, "SELECT F06 FROM T6011 WHERE F01=?" , userId);
			if (null != yqrm && !yqrm.equals("")) {
				int yqrId = selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6011 WHERE F07=?", yqrm);
				if (yqrId != 0 && txje.compareTo(BigDecimal.ZERO)==1) {
					yqrTxPresent(yqrId,txje);
				}
			}
		}
		update(id, status, check_reason);
	}

	private boolean checkStatus(WithdrawStatus status, int id) throws Throwable {
		String select = "SELECT F07 FROM T6034 WHERE F01=? FOR UPDATE";
		WithdrawStatus oldStatus = null;
		try (Connection connection = getConnection(ConsoleConst.DB_USER)) {
			try (PreparedStatement ps = connection.prepareStatement(select)) {
				ps.setInt(1, id);
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						oldStatus = EnumParser.parse(WithdrawStatus.class,
								rs.getString(1));
					}
				}
			}
		}
		if (oldStatus == null) {
			return true;
		}
		if ((status == WithdrawStatus.SHTG && oldStatus != WithdrawStatus.WSH)
				|| (status == WithdrawStatus.SHSB && oldStatus != WithdrawStatus.WSH)) {
			return true;
		}
		if ((status == WithdrawStatus.TXCG && oldStatus != WithdrawStatus.SHTG)
				|| (status == WithdrawStatus.TXSB && oldStatus != WithdrawStatus.SHTG)) {
			return true;
		}
		return false;
	}

	@Override
	public void checkBatch(WithdrawStatus status, String check_reason,
			int... ids) throws Throwable {
		serviceResource.openTransactions();
		if (ids == null || ids.length == 0) {
			throw new ParameterException("指定的记录不存在.");
		}
		String update = "UPDATE T6034 SET F07=?,F08=?,F09=?,F10=?,F11=?,F12=? WHERE F01=?";
		for (int id : ids) {
			if (id <= 0) {
				continue;
			}
			if (checkStatus(status, id)) {
				continue;
			}
			execute(getConnection(ConsoleConst.DB_USER), update, status,
					new Timestamp(System.currentTimeMillis()), check_reason,
					serviceResource.getSession().getAccountId(),
					serviceResource.getSession().getAccountId(), new Timestamp(
							System.currentTimeMillis()), id);
			update(id, status, check_reason);
		}
	}

	@Override
	public void export(TxglRecord[] txglRecords, OutputStream outputStream,
			String charset, String type) throws Throwable {
		if (outputStream == null) {
			return;
		}
		if (StringHelper.isEmpty(charset)) {
			charset = "GBK";
		}
		if (txglRecords == null) {
			return;
		}
		try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
				outputStream, charset))) {
			writer.write("流水号");
			writer.write(",");
			writer.write("用户名");
			writer.write(",");
			writer.write("真实姓名");
			writer.write(",");
			writer.write("提现银行");
			writer.write(",");
			writer.write("所在地");
			writer.write(",");
			writer.write("所在城市");
			writer.write(",");
			writer.write("所在支行");
			writer.write(",");
			writer.write("银行卡号");
			writer.write(",");
			writer.write("提现金额");
			writer.write(",");
			writer.write("手续费");
			writer.write(",");
			writer.write("实际到账金额");
			writer.write(",");
			writer.write("申请时间");
			writer.write(",");
			if (type.equals("wsh")) {
				writer.write("审核时间");
			} else if (type.equals("shtg")) {
				writer.write("审核时间");
			} else if (type.equals("ytx")) {
				writer.write("提现时间");
			} else if (type.equals("shsb")) {
				writer.write("审核时间");
			} else if (type.equals("txsb")) {
				writer.write("提现审核时间");
			}
			writer.write(",");
			writer.write("操作人");
			writer.newLine();
			for (TxglRecord txglRecord : txglRecords) {
				if (txglRecord == null) {
					continue;
				}
				writer.write(Integer.toString(txglRecord.id));
				writer.write(",");
				writer.write(txglRecord.loginName == null ? ""
						: txglRecord.loginName);
				writer.write(",");
				writer.write(txglRecord.userName == null ? ""
						: txglRecord.userName);
				writer.write(",");
				writer.write(txglRecord.extractionBank == null ? ""
						: txglRecord.extractionBank);
				writer.write(",");
				String[] loca = txglRecord.location.split(",");
				for (int i = 0; i < loca.length; i++) {
					writer.write(loca[i] == null ? "" : loca[i]);
				}
				writer.write(",");
				writer.write(txglRecord.cs == null ? ""
						: txglRecord.cs);
				writer.write(",");
				writer.write(txglRecord.subbranch == null ? ""
						: txglRecord.subbranch);
				writer.write(",");
				writer.write(";" + txglRecord.bankId + ";" == null ? "" : ";"
						+ txglRecord.bankId + ";");
				writer.write(",");
				writer.write(format(txglRecord.extractionAmount.add(txglRecord.charge)));
				writer.write(",");
				writer.write(format(txglRecord.charge));
				writer.write(",");
				writer.write(format(txglRecord.extractionAmount));
				writer.write(",");
				writer.write(" "
						+ DateParser.format(txglRecord.createTime,
								"yyyy-MM-dd HH:mm"));
				writer.write(",");
				if (!type.equals("wsh")) {
				writer.write(" "
						+ DateParser.format(txglRecord.checkDateTime,
								"yyyy-MM-dd HH:mm"));
				writer.write(",");
				}
				writer.write(txglRecord.approver == null ? ""
						: txglRecord.approver);
				writer.newLine();
			}
			writer.flush();
		}
	}

	@Override
	public Bank[] getBanks() throws Throwable {
		String sql = "SELECT F01,F02 FROM T6052";
		return selectAll(getConnection(ConsoleConst.DB_USER),
				new ArrayParser<Bank>() {

					@Override
					public Bank[] parse(ResultSet resultSet)
							throws SQLException {
						List<Bank> banks = new ArrayList<>();
						while (resultSet.next()) {
							Bank bank = new Bank();
							bank.id = resultSet.getInt(1);
							bank.name = resultSet.getString(2);
							banks.add(bank);
						}
						return banks.toArray(new Bank[banks.size()]);
					}
				}, sql);
	}

	@Override
	public Bank[] getBanks_() throws Throwable {
		String sql = "SELECT F01,F02 FROM T6052_1";
		return selectAll(getConnection(ConsoleConst.DB_USER),
				new ArrayParser<Bank>() {

					@Override
					public Bank[] parse(ResultSet resultSet)
							throws SQLException {
						List<Bank> banks = new ArrayList<>();
						while (resultSet.next()) {
							Bank bank = new Bank();
							bank.id = resultSet.getInt(1);
							bank.name = resultSet.getString(2);
							banks.add(bank);
						}
						return banks.toArray(new Bank[banks.size()]);
					}
				}, sql);
	}

	@Override
	public BankCard getCard(int id) throws Throwable {
		return select(
				getConnection(ConsoleConst.DB_USER),
				new ItemParser<BankCard>() {
					@Override
					public BankCard parse(ResultSet rs) throws SQLException {
						BankCard b = new BankCard();
						if (rs.next()) {
							b.bank = rs.getString(1);
							b.bankId = rs.getInt(2);
							b.id = rs.getInt(3);
							b.cardNumber = rs.getString(4);
							b.iswz = rs.getInt(5);
							b.isbind = rs.getInt(6);
							b.isll = rs.getInt(7);
							b.khdzid = rs.getString(8);
							b.khzh = rs.getString(9);
						}
						return b;
					}
				},
				"SELECT IFNULL(t6052.F02,t6052_1.F02) xtname,IFNULL(t6024.F03,t6024.F10) xtname,t6024.F01, t6024.F06 zh,t6024.F09,t6024.F11,IFNULL(t6024.F10,0),t6024.F04 khhdz,t6024.F05 khzh FROM t6024 LEFT JOIN t6052 ON t6052.F01 = t6024.F03 LEFT JOIN t6052_1 ON t6052_1.F01 = t6024.F10 WHERE t6024.F07 = 'QY' AND t6024.F02 = ? ORDER BY t6024.F08 DESC LIMIT 1 ",
				id);
	}

	@Override
	public BankCard getCard(String bankcard) throws Throwable {
		return select(
				getConnection(ConsoleConst.DB_USER),
				new ItemParser<BankCard>() {
					@Override
					public BankCard parse(ResultSet rs) throws SQLException {
						BankCard b = new BankCard();
						if (rs.next()) {
							b.bank = rs.getString(1);
							b.bankId = rs.getInt(2);
							b.id = rs.getInt(3);
							b.cardNumber = rs.getString(4);
							b.iswz = rs.getInt(5);
							b.isbind = rs.getInt(6);
							b.isll = rs.getInt(7);
							b.khdzid = rs.getString(8);
							b.khzh = rs.getString(9);
							b.status = rs.getString(10);
							b.acount = rs.getInt(11);
						}
						return b;
					}
				},
				"SELECT IFNULL(t6052.F02,t6052_1.F02) xtname,IFNULL(t6024.F03,t6024.F10) xtname,t6024.F01, t6024.F06 zh,t6024.F09,t6024.F11,IFNULL(t6024.F10,0),t6024.F04 khhdz,t6024.F05 khzh,t6024.F07 zt,t6024.F02 userid FROM t6024 LEFT JOIN t6052 ON t6052.F01 = t6024.F03 LEFT JOIN t6052_1 ON t6052_1.F01 = t6024.F10 WHERE t6024.F06 = ? ORDER BY t6024.F08 DESC LIMIT 1 ",
				bankcard);
	}

	@Override
	public void updateCard(int id, int userid, int bankid, int llbankid,
			String khdz, String khh, String yhkh) throws Throwable {
		execute(getConnection(ConsoleConst.DB_USER),
				"UPDATE T6024 SET F07='TY',F09=0,F11=0 WHERE F02=? ", userid);
		execute(getConnection(ConsoleConst.DB_USER),
				"UPDATE T6024 SET F02=?,F03=?,F04=?,F05=?,F06=?,F07='QY',F09=1,F10=?,F11=1 WHERE F01=?",
				userid, bankid == 0 ? "" : bankid, khdz, khh, yhkh,
				llbankid == 0 ? "" : llbankid,id);
		if(bankid == 0){
			execute(getConnection(ConsoleConst.DB_USER), "UPDATE T6024 SET  F03=NULL WHERE F01=?", id);
		}
		if(llbankid == 0){
			execute(getConnection(ConsoleConst.DB_USER), "UPDATE T6024 SET  F10=NULL WHERE F01=?", id);
		}
	}

	@Override
	public void addCard(int userid, int bankid, int llbankid, String khdz,
			String khh, String yhkh) throws Throwable {
		execute(getConnection(ConsoleConst.DB_USER),
				"UPDATE T6024 SET F07='TY',F09=0,F11=0 WHERE F02=? ", userid);
		int id = insert(getConnection(ConsoleConst.DB_USER),
				"INSERT INTO T6024 SET F02=?,F03=?,F04=?,F05=?,F06=?,F07='QY',F09=1,F08=CURRENT_TIMESTAMP(),F10=?,F11=1 ",
				userid, bankid == 0 ? "" : bankid, khdz, khh, yhkh,
				llbankid == 0 ? "" : llbankid);
		if(bankid == 0){
			execute(getConnection(ConsoleConst.DB_USER), "UPDATE T6024 SET F03=NULL WHERE F01=?", id);
		}
		if(llbankid == 0){
			execute(getConnection(ConsoleConst.DB_USER), "UPDATE T6024 SET F10=NULL WHERE F01=?", id);
		}
	}
	
	private void yqrTxPresent(int yqrId,BigDecimal txje) throws Throwable {

		String sql = "SELECT F02 FROM _1010 WHERE F01=?";
		
		BigDecimal txyqrzs = selectBigDecimal(P2PConst.DB_CONFIG, sql, "SYSTEM.TXYQRZS");
		
		if (txyqrzs.compareTo(BigDecimal.ZERO)!=1) {
			return;
		}
		BigDecimal zsje = txje.multiply(txyqrzs.divide(new BigDecimal("100"),4,RoundingMode.HALF_UP));
		execute(getConnection(P2PConst.DB_USER),
				"INSERT INTO T6023 SET F01=?,F02=?,F04=? ON DUPLICATE KEY UPDATE F02=F02+?,f03=0,F04=F04+?",
				yqrId,zsje, zsje, zsje, zsje);
//			execute(getConnection(P2PConst.DB_CONSOLE),
			
	}
	
	
}
